import { DatabaseManager } from '../database';
import { PhotoRecord } from '../schema';
import { randomUUID } from 'crypto';

/**
 * 照片数据访问层
 * 提供照片相关的数据库操作方法
 */
export class PhotoRepository {
  constructor(private database: DatabaseManager) {}

  /**
   * 根据哈希值查询照片
   */
  async getPhotoByHash(hash: string): Promise<PhotoRecord | null> {
    const result = this.database.queryOne<PhotoRecord>(
      'SELECT * FROM photos WHERE filehash = ? AND deleted = 0',
      [hash]
    );
    return result || null;
  }

  /**
   * 根据文件路径查询照片
   */
  async getPhotoByPath(filepath: string): Promise<PhotoRecord | null> {
    const result = this.database.queryOne<PhotoRecord>(
      'SELECT * FROM photos WHERE filepath = ? AND deleted = 0',
      [filepath]
    );
    return result || null;
  }

  /**
   * 根据ID查询照片
   */
  async getPhotoById(id: string): Promise<PhotoRecord | null> {
    const result = this.database.queryOne<PhotoRecord>(
      'SELECT * FROM photos WHERE id = ? AND deleted = 0',
      [id]
    );
    return result || null;
  }

  /**
   * 插入新照片
   */
  async insertPhoto(photoData: Omit<PhotoRecord, 'id' | 'imported_at' | 'updated_at'>): Promise<string> {
    const id = randomUUID();
    const now = new Date().toISOString();
    
    const sql = `
      INSERT INTO photos (
        id, filename, filepath, filesize, filehash, mimetype,
        width, height, created_at, modified_at, taken_at,
        imported_at, updated_at,
        camera_make, camera_model, lens_model, focal_length,
        aperture, shutter_speed, iso, flash,
        latitude, longitude, altitude, location_name,
        favorite, rating, tags, description,
        thumbnail_generated, metadata_extracted, deleted
      ) VALUES (
        ?, ?, ?, ?, ?, ?,
        ?, ?, ?, ?, ?,
        ?, ?,
        ?, ?, ?, ?,
        ?, ?, ?, ?,
        ?, ?, ?, ?,
        ?, ?, ?, ?,
        ?, ?, ?
      )
    `;

    const params = [
      id,
      photoData.filename,
      photoData.filepath,
      photoData.filesize,
      photoData.filehash,
      photoData.mimetype,
      photoData.width,
      photoData.height,
      photoData.created_at,
      photoData.modified_at,
      photoData.taken_at || null,
      now, // imported_at
      now, // updated_at
      photoData.camera_make || null,
      photoData.camera_model || null,
      photoData.lens_model || null,
      photoData.focal_length || null,
      photoData.aperture || null,
      photoData.shutter_speed || null,
      photoData.iso || null,
      photoData.flash ? 1 : 0,
      photoData.latitude || null,
      photoData.longitude || null,
      photoData.altitude || null,
      photoData.location_name || null,
      photoData.favorite ? 1 : 0,
      photoData.rating || 0,
      photoData.tags || '[]',
      photoData.description || null,
      photoData.thumbnail_generated ? 1 : 0,
      photoData.metadata_extracted ? 1 : 0,
      photoData.deleted ? 1 : 0
    ];

    this.database.execute(sql, params);
    return id;
  }

  /**
   * 更新照片信息
   */
  async updatePhoto(id: string, updates: Partial<PhotoRecord>): Promise<boolean> {
    const fields = [];
    const params = [];

    // 构建动态更新字段
    for (const [key, value] of Object.entries(updates)) {
      if (key !== 'id') {
        fields.push(`${key} = ?`);
        params.push(value);
      }
    }

    if (fields.length === 0) {
      return false;
    }

    // 添加更新时间
    fields.push('updated_at = ?');
    params.push(new Date().toISOString());
    params.push(id);

    const sql = `UPDATE photos SET ${fields.join(', ')} WHERE id = ?`;
    const result = this.database.execute(sql, params);
    
    return result.changes > 0;
  }

  /**
   * 删除照片（软删除）
   */
  async deletePhoto(id: string): Promise<boolean> {
    const result = this.database.execute(
      'UPDATE photos SET deleted = 1, updated_at = ? WHERE id = ?',
      [new Date().toISOString(), id]
    );
    return result.changes > 0;
  }

  /**
   * 获取照片列表
   */
  async getPhotos(options: {
    limit?: number;
    offset?: number;
    search?: string;
    sortBy?: 'taken_at' | 'imported_at' | 'filename';
    sortOrder?: 'ASC' | 'DESC';
  } = {}): Promise<PhotoRecord[]> {
    const {
      limit = 100,
      offset = 0,
      search = '',
      sortBy = 'imported_at',
      sortOrder = 'DESC'
    } = options;

    let sql = 'SELECT * FROM photos WHERE deleted = 0';
    const params: any[] = [];

    // 添加搜索条件
    if (search) {
      sql += ' AND (filename LIKE ? OR description LIKE ? OR location_name LIKE ?)';
      const searchPattern = `%${search}%`;
      params.push(searchPattern, searchPattern, searchPattern);
    }

    // 添加排序
    sql += ` ORDER BY ${sortBy} ${sortOrder}`;

    // 添加分页
    sql += ' LIMIT ? OFFSET ?';
    params.push(limit, offset);

    return this.database.query<PhotoRecord>(sql, params);
  }

  /**
   * 获取照片总数
   */
  async getPhotosCount(search?: string): Promise<number> {
    let sql = 'SELECT COUNT(*) as count FROM photos WHERE deleted = 0';
    const params: any[] = [];

    if (search) {
      sql += ' AND (filename LIKE ? OR description LIKE ? OR location_name LIKE ?)';
      const searchPattern = `%${search}%`;
      params.push(searchPattern, searchPattern, searchPattern);
    }

    const result = this.database.queryOne<{ count: number }>(sql, params);
    return result?.count || 0;
  }

  /**
   * 搜索照片
   */
  async searchPhotos(query: string, limit: number = 50): Promise<PhotoRecord[]> {
    const searchPattern = `%${query}%`;
    const sql = `
      SELECT * FROM photos 
      WHERE deleted = 0 
        AND (
          filename LIKE ? 
          OR description LIKE ? 
          OR location_name LIKE ?
          OR camera_make LIKE ?
          OR camera_model LIKE ?
        )
      ORDER BY taken_at DESC, imported_at DESC
      LIMIT ?
    `;

    return this.database.query<PhotoRecord>(sql, [
      searchPattern,
      searchPattern,
      searchPattern,
      searchPattern,
      searchPattern,
      limit
    ]);
  }

  /**
   * 批量导入照片
   */
  async importPhotos(photos: Omit<PhotoRecord, 'id' | 'imported_at' | 'updated_at'>[]): Promise<{ success: boolean; count: number; errors: string[] }> {
    const errors: string[] = [];
    let successCount = 0;

    return this.database.transaction(() => {
      for (const photoData of photos) {
        try {
          this.insertPhoto(photoData);
          successCount++;
        } catch (error) {
          const errorMessage = error instanceof Error ? error.message : '未知错误';
          errors.push(`导入 ${photoData.filename} 失败: ${errorMessage}`);
        }
      }

      return {
        success: errors.length === 0,
        count: successCount,
        errors
      };
    });
  }

  /**
   * 更新缩略图生成状态
   */
  async updateThumbnailStatus(id: string, generated: boolean): Promise<boolean> {
    const result = this.database.execute(
      'UPDATE photos SET thumbnail_generated = ?, updated_at = ? WHERE id = ?',
      [generated ? 1 : 0, new Date().toISOString(), id]
    );
    return result.changes > 0;
  }

  /**
   * 更新元数据提取状态
   */
  async updateMetadataStatus(id: string, extracted: boolean): Promise<boolean> {
    const result = this.database.execute(
      'UPDATE photos SET metadata_extracted = ?, updated_at = ? WHERE id = ?',
      [extracted ? 1 : 0, new Date().toISOString(), id]
    );
    return result.changes > 0;
  }
}